import java.io.FileReader;
import java.io.BufferedReader;
import java.io.IOException;

import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PopulatingDataBase extends DataBase
{
/**Subclass of database with functions to read base data in from files + create tables
 * @author JMR 27/5/2010
 */
  private String base_filename = "data";
  private String player_extension = "players";
  private String team_extension = "teams";
  private String squad_extension = "squads";
  private String side_extension = "sides";  

  public void setup_database ()
  {
    try{
      create_tables();
      populate();
    }
    catch (SQLException e){
      e.printStackTrace();
      System.exit(1);
    }
    catch (IOException e){
      e.printStackTrace();
      System.exit(1);
    }
  }

  private void create_tables() throws SQLException
  {
    // Create a Statement object with which we can execute SQL commands

    Statement statement = database.createStatement();

    // Drop existing tables, if present

    try {
      statement.executeUpdate("DROP TABLE players");
    }
    catch (SQLException error) {
      // Catch and ignore SQLException, as this just indicates
      // that the table didn't exist in the first place!
    }

    try {
      statement.executeUpdate("DROP TABLE teams");
    } catch (SQLException error) {}

    // Create a fresh players table
    statement.executeUpdate("CREATE TABLE players ("
                          + "id INTEGER NOT NULL PRIMARY KEY,"
                          + "name VARCHAR(60) NOT NULL,"
                          + "attack INTEGER NOT NULL,"
                          + "defence INTEGER NOT NULL,"
                          + "goalkeeping INTEGER NOT NULL,"
                          + "passing INTEGER NOT NULL");

    // Create a fresh teams table
    statement.executeUpdate("CREATE TABLE teams ("
                          + "id INTEGER NOT NULL PRIMARY KEY,"
                          + "name VARCHAR(60) NOT NULL,"
                          + "manager INTEGER NOT NULL)");

    //create the linker tables
    // Create a fresh squads table
    statement.executeUpdate("CREATE TABLE squads ("
                          + "team INTEGER NOT NULL FOREIGN KEY REFERENCES teams,"
                          + "player INTEGER NOT NULL FOREIGN KEY REFERENCES players");    

    // Create a fresh playing sides table
    statement.executeUpdate("CREATE TABLE sides ("
                          + "team INTEGER NOT NULL FOREIGN KEY REFERENCES teams,"
                          + "player INTEGER NOT NULL FOREIGN KEY REFERENCES players,"
                          + "forwardposition VARCHAR(30) NOT NULL,"
                          + "sideposition VARCHAR(30) NOT NULL");

    statement.close();
  }

  private void populate () throws IOException, SQLException
  {
    FileReader reader = new FileReader(String.format("%s.%s",base_filename,player_extension)); 
    BufferedReader input = new BufferedReader(reader);

    CharString read_buffer = CharString.read_into(input);
    read_buffer.set_delimiter('\n');
    CharString line_buffer = new CharString ();

    PreparedStatement statement =
       database.prepareStatement("INSERT INTO players VALUES(?,?,?,?,?,?)");

    while (read_buffer.hasNext()){
      read_buffer.next(line_buffer);

      String name = line_buffer.next();
      int attack = Integer.parseInt(line_buffer.next());
      int defence = Integer.parseInt(line_buffer.next());;
      int keeping = Integer.parseInt(line_buffer.next());;
      int passing = Integer.parseInt(line_buffer.next());;

      // Insert data into table
      statement.setInt(1, next_player_id);
      statement.setString(2, name);
      statement.setInt(3, attack);
      statement.setInt(4, defence);
      statement.setInt(5, keeping);
      statement.setInt(6, passing);
      statement.executeUpdate();
      next_player_id++;
    }
    statement.close();
    input.close();

    //next do teams
    reader = new FileReader(String.format("%s.%s",base_filename,team_extension)); 
    input = new BufferedReader(reader);

    read_buffer = CharString.read_into(input);
    read_buffer.set_delimiter('\n');
    line_buffer = new CharString ();

    statement = database.prepareStatement("INSERT INTO teams VALUES(?,?,?)");

    while (read_buffer.hasNext()){
      read_buffer.next(line_buffer);

      String name = line_buffer.next();
      int manager = Integer.parseInt(line_buffer.next());

      // Insert data into table
      statement.setInt(1, next_team_id);
      statement.setString(2, name);
      statement.setInt(3, manager);
      statement.executeUpdate();
      next_team_id++;
    }
    statement.close();
    input.close();

    //then do squads
    reader = new FileReader(String.format("%s.%s",base_filename,squad_extension)); 
    input = new BufferedReader(reader);

    read_buffer = CharString.read_into(input);
    read_buffer.set_delimiter('\n');
    line_buffer = new CharString ();

    statement = database.prepareStatement("INSERT INTO squads VALUES(?,?)");

    while (read_buffer.hasNext()){
      read_buffer.next(line_buffer);

      int team = Integer.parseInt(line_buffer.next());
      int player = Integer.parseInt(line_buffer.next());

      // Insert data into table
      statement.setInt(1, team);
      statement.setInt(2, player);
      statement.executeUpdate();
    }
    statement.close();
    input.close();

    //finally do sides
    reader = new FileReader(String.format("%s.%s",base_filename,side_extension)); 
    input = new BufferedReader(reader);

    read_buffer = CharString.read_into(input);
    read_buffer.set_delimiter('\n');
    line_buffer = new CharString ();

    statement = database.prepareStatement("INSERT INTO sides VALUES(?,?,?,?)");

    while (read_buffer.hasNext()){
      read_buffer.next(line_buffer);

      int team = Integer.parseInt(line_buffer.next());
      int player = Integer.parseInt(line_buffer.next());
      String forward_pos = line_buffer.next();
      String side_pos = line_buffer.next();

      // Insert data into table
      statement.setInt(1, team);
      statement.setInt(2, player);
      statement.setString(3, forward_pos);
      statement.setString(4, side_pos);
      statement.executeUpdate();
    }
    statement.close();
    input.close();

  }



}
